package com.suoluo.zenadmin.dao.hotel;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;

import com.suoluo.zenadmin.service.crawler.bean.HotelReviewBean;
import com.suoluo.zenadmin.vo.hotel.Hotel2VO;
import com.suoluo.zenadmin.vo.hotel.HotelResource2VO;
import com.suoluo.zenadmin.vo.hotel.HotelReviewRecordVO;
import com.suoluo.zenadmin.vo.hotel.HotelReviewVO;
import com.suoluo.zenadmin.vo.hotel.HotelServicesCategoryVO;
import com.suoluo.zenadmin.vo.hotel.HotelServicesVO;
import com.suoluo.zenadmin.vo.hotel.HotelStyleVO;
import com.suoluo.zenadmin.vo.hotel.HotelTagVO;

@DAO
public interface HotelDAO {

	@SQL("select * from tb_hotel_services_category")
	public List<HotelServicesCategoryVO> findListHotelServiceCategory();

	@SQL("insert into tb_hotel_services_category(t_service_category_oss_icon,t_service_category_tip) values(:1.serviceCategoryOssIcon,:1.serviceCategoryTip)")
	public void saveHotelServiceCategory(HotelServicesCategoryVO servicePOJO);

	@SQL("SELECT * FROM tb_hotel_services_category where t_service_category_id = :1")
	public HotelServicesCategoryVO findServiceCategoryById(int serviceId);

	@SQL("update tb_hotel_services_category set t_service_category_oss_icon=:1.serviceCategoryOssIcon,t_service_category_tip=:1.serviceCategoryTip where t_service_category_id=:1.serviceCategoryId")
	public void updateHotelServiceCategory(HotelServicesCategoryVO serviceVO);

	@SQL("delete from tb_hotel_services_category where t_service_category_id=:1")
	public void deleteHotelServiceCategory(int serviceId);

	@SQL("SELECT * FROM tb_hotel_services where t_service_category_id=:1")
	public List<HotelServicesVO> findHotelLinkServiceCategory(int categoryId);

	@SQL("select * from tb_hotel_style")
	public List<HotelStyleVO> findListHotelStyle();

	@SQL("insert into tb_hotel_style(t_style_name) values(:1.styleName)")
	public void saveHotelStyle(HotelStyleVO stylePOJO);

	@SQL("select * from tb_hotel_style where t_style_id=:1")
	public HotelStyleVO findHotelStyleById(int styleId);

	@SQL("update tb_hotel_style set t_style_name=:1.styleName where t_style_id=:1.styleId")
	public void updateHotelStyle(HotelStyleVO stylePOJO);

	@SQL("select count(t_hotel_tag_id) from tb_hotel_tag where t_style_id=:1")
	public int findHotelTag(int styleId);

	@SQL("delete from tb_hotel_style where t_style_id=:1")
	public void deleteHotelStyle(int styleId);

	@SQL("select * from tb_hotel2 where dest_id = :3 order by hotel_id desc  limit :1,:2")
	public List<Hotel2VO> findListHotel(int start, int rows, int destId);

	@SQL("select count(hotel_id) from tb_hotel2 where dest_id=:1")
	public int findHotelCountByDestId(int destId);

	@ReturnGeneratedKeys
	@SQL("insert into tb_hotel2(dest_id,address,tel,score,houses,price,email,url,"
			+ "zhname,enname,special,honor,location,lat,lng,summary,logo,copyright,viewnum,view_style) "
			+ "values(:1.destId,:1.address,:1.tel,:1.score,:1.houses,"
			+ ":1.price,:1.email,:1.url,:1.zhname,:1.enname,"
			+ ":1.special,:1.honor,:1.location,:1.lat,:1.lng,"
			+ ":1.summary,:1.logo,:1.copyright,:1.viewnum,:1.viewStyle)")
	public int saveHotel(Hotel2VO hotelVO);

//	@SQL("update tb_hotel set t_hotel_main_img=:1.hotelMainImg,t_hotel_img_preffix=:1.hotelImgPreffix,"
//			+ "t_hotel_img1=:1.hotelImg1,t_hotel_img2=:1.hotelImg2,t_hotel_logo=:1.hotelLogo "
//			+ " where t_hotel_id=:1.hotelId")
//	public void updateHotelImgs(HotelVO hotelVO);

	@SQL("insert into tb_hotel_services(t_hotel_id,t_service_category_id) values(:1.hotelId,:1.serviceCategoryId)")
	public void saveHotelServices(List<HotelServicesVO> list);

	@SQL("insert into tb_hotel_tag(t_hotel_id,t_style_id) values(:1.hotelId,:1.styleId)")
	public void saveHotelStyles(List<HotelTagVO> listStyle);

//	@SQL("select * from tb_hotel where t_hotel_id=:1")
//	public HotelPOJO findHotelById(int hotelId);
	
	@SQL("select * from tb_hotel2 t1 join tb_base_info t2 on t1.hotel_id = t2.data_id and t2.info_type = :2 " +
			"join tb_image t3 t3.image_id = t1.image_id where t1.hotel_id = :1")
	public Hotel2VO findHotelById(int hotelId,int infoType);
	
	@SQL("select * from tb_hotel2 where hotel_id = :1")
	public Hotel2VO findSimplestHotelById(int hotelId);
	
	@SQL("select * from tb_hotel2 t1 join tb_base_info t2 on t1.hotel_id = t2.data_id and t2.info_type = :2 where t1.hotel_id = :1 and t2.default_info=1")
	public Hotel2VO findHotelWithoutImageById(int hotelId,int infoType);

	@SQL("update tb_hotel2 set zhname=:1.zhname,enname=:1.enname,lat=:1.lat,lng=:1.lng," +
			"address=:1.address,tel=:1.tel,summary=:1.summary,score=:1.score,houses=:1.houses," +
			"price=:1.price,email=:1.email,url=:1.url,special=:1.special,honor=:1.honor," +
			"location=:1.location,logo=:1.logo,copyright=:1.copyright,view_style=:1.viewStyle " +
			" where hotel_id=:1.hotelId")
	public void updateHotel(Hotel2VO hotelVO);

	@SQL("select sum(num) from (select count(hotel_id) num  from tb_hotel_resource2 "
			+ "where hotel_id= :1 UNION all SELECT count(t_link_id) num from tb_home_item "
			+ "where t_category_id in (3,5) and t_link_id = :1) aa")
	public int findHotelForeignKeyCount(int hotelId);

	@SQL("delete from tb_hotel_review_record where t_review_id in "
			+ "(select t_review_id from tb_hotel_review where t_hotel_id=:1)")
	public void deleteHotelReviewRecord(int hotelId);

	@SQL("delete from tb_hotel_review where t_hotel_id=:1")
	public void deleteHotelReview(int hotelId);

	@SQL("delete from tb_hotel_services where t_hotel_id= :1")
	public void deleteHotelServices(int hotelId);

	@SQL("delete from tb_hotel_tag where t_hotel_id= :1")
	public void deleteHotelAndStyle(int hotelId);

	@SQL("delete from tb_dest_feature_item2 where rel_id =:1")
	public void deleteDestFeatureItem(int hotelId);

	@SQL("delete from tb_hotel2 where hotel_id=:1")
	public void deleteHotel(int hotelId);

	@SuppressWarnings("rawtypes")
	@SQL("select  c.t_service_category_id serviceCategoryId,c.t_service_category_oss_icon serviceCategoryOssIcon,"
			+ "c.t_service_category_tip serviceCategoryTip,if(isnull(t.t_service_category_id),0,1) link "
			+ "from tb_hotel_services t right join tb_hotel_services_category c on t.t_service_category_id = c.t_service_category_id "
			+ " and t.t_hotel_id = :1")
	public List<Map> findListHotelAndServices(int hotelId);

	@SuppressWarnings("rawtypes")
	@SQL("select ts.t_style_id styleId,ts.t_style_name styleName, "
			+ "if(isnull(tf.t_style_id),0,1) link from "
			+ "tb_hotel_tag tf right join tb_hotel_style ts "
			+ "on tf.t_style_id = ts.t_style_id  "
			+ "and tf.t_hotel_id =:1 order by ts.t_style_id")
	public List<Map> findListHotelAndStyles(int hotelId);

	@ReturnGeneratedKeys
	@SQL("Insert into tb_hotel_resource2(hotel_id,resource_type,summary,zhname,enname) " +
			"values(:1.hotelId,:1.resourceType,:1.summary,:1.zhname,:1.enname)")
	public int saveHotelResource(HotelResource2VO resource);

	@SQL("update tb_hotel_resource2 set image_id = :2 where resource_id=:1")
	public void updateResourceRelImg(int resourceId,int imageId);

//	@SQL("insert into tb_hotel_resource_item(t_resource_id,t_resource_orig_img,t_resource_mini_img) "
//			+ "values(:1.resourceId,:1.resourceOrigImg,:1.resourceMiniImg)")
//	public void saveHotelResourceItem(List<HotelResourceItemPOJO> list);

	@SQL("select * from tb_hotel_resource2 t1 join tb_base_info t2 on t1.resource_id = t2.data_id and t2.info_type = :2 " +
			"join tb_image t3 on t3.image_id = t1.image_id where t1.resource_id=:1")
	public HotelResource2VO findHotelResourceById(int reosurceId,int infoType);
	
	@SQL("select * from tb_hotel_resource2 t1 " +
			"join tb_base_info t2 on t1.resource_id = t2.data_id and t2.info_type = :2 where t1.resource_id=:1")
	public HotelResource2VO findHotelResourceWithoutImageById(int reosurceId,int infoType);

	@SQL("select * from tb_hotel_resource2 t1 join tb_image t2 on t1.image_id = t2.image_id where t1.hotel_id=:1")
	public List<HotelResource2VO> findHotelResourcesWithoutContentByHotelId(int hotelId);

//	@SQL("select * from tb_hotel_resource_item where t_resource_id=:1")
//	public List<HotelResourceItemPOJO> findListHotelResourceItem(int resourceId);

	@SQL("update tb_hotel_resource2 set zhname=:1.zhname,enname=:1.enname," +
			"resource_type=:1.resourceType,summary=:1.summary" +
			" where resource_id=:1.resourceId")
	public void updateHotelResource(HotelResource2VO resource);

//	@SQL("delete from tb_hotel_resource_item where t_resource_id=:1")
//	public void deleteHotelResourceItem(int resourceId);

	@SQL("delete from tb_hotel_resource2 where resource_id=:1")
	public void deleteHotelResource(int resourceId);

//	@SQL("delete from tb_hotel_resource_item where t_resource_item_id=:1")
//	public void deleteHotelResourceItemById(int itemId);

	@SQL("SELECT * FROM tb_hotel_review where t_hotel_id=:1 order by t_review_type asc")
	public List<HotelReviewVO> findListHotelReview(int hotelId);
	@ReturnGeneratedKeys
	@SQL("insert into tb_hotel_review(t_hotel_id,t_review_type,t_review_url,t_crawler_time) "
			+ "values(:1.hotelId,:1.reviewType,:1.reviewUrl,:1.crawlerTime)")
	public int saveHotelReview(HotelReviewVO review);

	@SQL("select * from tb_hotel_review where t_review_id=:1")
	public HotelReviewVO findHotelReviewById(int reviewId);

	@SQL("update tb_hotel_review set t_review_url=:1.reviewUrl where t_review_id=:1.reviewId")
	public void updateHotelReview(HotelReviewVO review);

	@SQL("delete from tb_hotel_review_record where t_review_id=:1")
	public void deleteHotelReviewRecordByReviewId(int reviewId);

	@SQL("delete from tb_hotel_review where t_review_id=:1")
	public void deleteHotelReviewByReviewId(int reviewId);

	@SQL("select * from tb_hotel_review_record where t_review_id=:1")
	public List<HotelReviewRecordVO> findListHotelReviewRecord(int reviewId);
	
	@SQL("select * from tb_hotel_review where t_crawler_status=:1")
	public List<HotelReviewVO> findInterruptReview(int crawlerStatus);
	
	@SQL("select * from tb_hotel_review where t_crawler_time <= :1")
	public List<HotelReviewVO> findExpireReview(int holdDays);
	@SQL("update tb_hotel_review set t_crawler_status=:1 where t_review_id=:2")
	public void updateCrawlerState(int stats,int reviewId);
	@SQL("update tb_hotel_review set t_crawler_fail_count=t_crawler_fail_count+1,t_crawler_status = :1 "
					+ "where t_review_id =:2")
	public void updateIncrementFailCountAndSetFail(int failStatus,int reviewId);
	
	@SQL("update tb_hotel_review set t_review_count=:1.reviewCount,t_review_score=:1.reviewScore,"
					+ "t_review_full_score=:1.reviewFullScore,t_review_level=:1.reviewLevel,t_review_rank=:1.reviewRank,"
					+ "t_crawler_success_count=t_crawler_success_count+1,"
					+ "t_crawler_time=:1.crawlerTime,t_crawler_status=:1.crawlerStatus where t_review_id =:1.reviewId")
	public void updateCrawlerReviewInfo(HotelReviewBean review);
	@SQL("select count(t_review_id) from tb_hotel_review"
					+ " where t_hotel_id=:1 and t_review_type=:2")
	public int findHotelReviewByReviewType(int hotelId,int reviewType);
	
	@SQL("insert into tb_hotel_review_record(t_review_id,t_record_title," +
			"t_record_desc,t_record_autor,t_record_time,t_record_score," +
			"t_record_full_score) values(:1.reviewId,:1.recordTitle," +
			":1.recordDesc,:1.recordAutor,:1.recordTime,:1.recordScore,:1.recordFullScore)")
	public void saveHotelReviewRecord(List<HotelReviewRecordVO> listRecord);
	
	@SQL("select * from tb_hotel2 t1 join tb_image t2 on t1.image_id = t2.image_id where t1.dest_id=:1")
	public List<Hotel2VO> findHotelsWithoutContentByDestId(int destId);
	
	@SQL("select c.* from tb_hotel_services s join tb_hotel_services_category c"
					+ " on s.t_service_category_id = c.t_service_category_id where s.t_hotel_id = :1")
	public List<HotelServicesCategoryVO> findHotelServicesByHotelId(int hotelId);
	
	@SQL("select * from tb_hotel2 t1 join tb_image t2 on t1.image_id = t2.image_id " +
			"where t1.hotel_id <> :1 and t1.style_id=:2 limit 0,:3")
	public List<Hotel2VO> findSamilarHotels(int hotelId,int styleId,int size);
	
	@SQL("select * from tb_hotel2 t1 join tb_image t2 on t1.image_id = t2.image_id " +
	"where t1.hotel_id <> :1.hotelId and t1.dest_id = :1.destId limit 0,:2")
	public List<Hotel2VO> findSamilarHotels(Hotel2VO hotel,int size);
	
	@SQL("select ts.t_style_id,t_style_name from tb_hotel_tag  tg join " +
					"tb_hotel_style ts on tg.t_style_id = ts.t_style_id where t_hotel_id = :1 ")
	public List<HotelStyleVO> findHotelStyles(int hotelId);
	
	
	@SQL("select * from tb_hotel_resource2 t1 join tb_image t2 on t1.image_id = t2.image_id " +
					"where t1.hotel_id = :1 and t1.resource_type = :2")
	public List<HotelResource2VO> findHotelResourcesWithoutContentByHotelId(int hotelId,int resourceType);
	
	@SQL("select * from tb_hotel_resource2 where resource_id = :1")
	public HotelResource2VO findSimplestHotelResourceById(int resourceId);
	
//	@SuppressWarnings("rawtypes")
//	@SQL("select " +
//			"dfi.feature_item_id,dfi.zhname,dfi.enname,dfi.rel_id,dfi.lat,dfi.lng," +
//			"t4.source,t4.small,"+
//			"dfc.t_feature_category_order,"+
//			"dfc.t_feature_category_id," +
//			"dfc.t_feature_category_name," +
//			"dfc.t_feature_category_color"+
//		  " from tb_dest_feature2 df" +
//		  " join tb_dest_feature_category dfc ON dfc.t_feature_category_id = df.feature_category_id" +
//		  " join tb_dest_feature_item2 dfi ON df.feature_id = dfi.feature_id" +
//		  " join tb_image t4 on dfi.image_id = t4.image_id" +
//		  " where  df.dest_id = :1")
//	public List<Map> findHotelAroundData(int destId);
	
	@SQL("update tb_dest_feature_item2 set zhname=:1.zhname,enname=:1.enname," +
			"lat=:1.lat,lng=:1.lng,summary=:1.summary,tel=:1.tel," +
			"url=:1.url,address=:1.address,price=:1.price,logo=:1.logo,email=:1.email" +
			" where rel_id=:1.hotelId")
	public void synHotelToFeatureItem(Hotel2VO hotelVO);
	
	@SQL("select feature_item_id from tb_dest_feature_item2 where rel_id =:1")
	public Integer findDestFeatureItemId(int hotelId);

	@SQL("delete from tb_select_feature_item where t_feature_item_id=:1")
	public void deleteSelectFeatureItem(int itemId);
	@SQL("update tb_hotel2 set image_id = :2 where hotel_id = :1")
	public void updateHotelImageId(int hotelId,int imageId);
	
	
	@SQL("update tb_hotel2 set viewnum = viewnum+1 where hotel_id = :1")
	public void updateIncrementHotelViewnum(int hotelId);
	
	@SQL("select * from tb_hotel2 t1 join tb_image t2 on t1.image_id = t2.image_id order by t1.hotel_id desc limit :1,:2")
	public List<Hotel2VO> findNewHotels(int startPos,int pageCount);
	
	@SQL("select * from tb_hotel2 t1 join tb_image t2 on t1.image_id = t2.image_id order by t1.viewnum desc limit :1,:2")
	public List<Hotel2VO> findHotHotels(int startPos,int pageCount);
	
	@SQL("select * from tb_hotel2 where dest_id=:1")
	public List<Hotel2VO> findAllHotel(int destId);
	
	@SQL("select * from tb_hotel_resource2 t1 join tb_image t2 on t1.image_id = t2.image_id " +
			" join tb_base_info inf on inf.data_id = t1.resource_id and inf.info_type=:2 " +
	" where t1.hotel_id = :1")
	public List<HotelResource2VO> findHotelResources(int hotelId,int infoType);
}
